import openpyxl
import pymysql
workbook = openpyxl.load_workbook('user.xlsx')
#worksheet=workbook.create_sheet(title='one sheet')
worksheet=workbook['one sheet']
data=[
    ['编号', '身份证号', '用户名', '姓名', '密码', '手机号', '邮箱','年龄', '性别', '地址',
     "入职时间",'薪水', '职业', '公司'],
    [1,'140923220101100013','风吹麦浪','张三','123456','13403501234','296754324@qq.com',18,'男','郑州',"2025-06-07",1000,'医生','第二人民医院'],
    [2, '14092322010110213', '凤栖梧桐', '李娜', '234567', '15688781234', '296754987@qq.com',20, '女', '焦作',"2025-03-04", 5000, '教师', '第二中学'],
    [3, '14092767740110213', '玫瑰星球', '李四', '239967', '18276812341', '299554387@qq.com', 23, '男', '杭州',
     "2025-01-26", 4000, '教师', '第三中学']
]
'''
for i in data:
    worksheet.append(i)
workbook.save('user.xlsx')
'''
db=pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='20021224',
    charset='utf8',
    database='person_massege'
)
cursor=db.cursor()
sql="insert into excel_message values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
try:
    with db.cursor() as cursor:
        for i in worksheet.iter_rows(min_row=2, max_row=4, min_col=1, max_col=14):
            li = []
            for j in i:
                #print(j.value, end='\t')
                li.append(j.value)
            cursor.execute(sql,li)
        db.commit()
        print('数据操作成功！')
except Exception as e:
    print('数据操作失败！', e)
finally:
    db.close()

